package org.demo.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.demo.dao.IProductDAO;
import org.demo.entity.CstCustomer;
import org.demo.entity.Product;
import org.demo.entity.Storage;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class ProductDAOImpl extends HibernateDaoSupport implements IProductDAO {

	public List<Product> findProducts(final int pageNo, final int pageSize, final Product product) {

		 
		return (List<Product>) getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session s) throws HibernateException, SQLException {

				String hql="from Product p where p.prodName like :prodName " +
				"and p.prodType like :prodType and p.prodBatch like :prodBatch";
				
				Query query=s.createQuery(hql);
				
				query.setString("prodName", "%"+product.getProdName()+"%");
				query.setString("prodType", "%"+product.getProdType()+"%");
				query.setString("prodBatch", "%"+product.getProdBatch()+"%");
				int firstResultIndex=pageSize*(pageNo-1);
				 
				query.setFirstResult(firstResultIndex);
				query.setMaxResults(pageSize);
				

				return query.list();
			}});
	}

	public Integer countProducts(final int pageNo, final int pageSize, final Product product) {

		 

		String hql="select count(p) from Product p where p.prodName like :prodName " +
		"and p.prodType like :prodType and p.prodBatch like :prodBatch";
		List list=getHibernateTemplate().findByNamedParam(hql, new String[]{"prodName","prodType","prodBatch"}
		, new String[]{"%"+product.getProdName()+"%","%"+product.getProdType()+"%","%"+product.getProdBatch()+"%"});
	 
		if (list!=null) {
			return (Integer) list.get(0);
		}
		
		return 0;
		
	}

	public List<Storage> findStorages(final int pageNo, final int pageSize, final Storage storage) {

		 
		return (List<Storage>) getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session s) throws HibernateException, SQLException {

				String hql="from Storage p where p.product.prodName like :prodName " +
				"and p.stkWarehouse like :stkWarehouse";
				
				Query query=s.createQuery(hql);
				
				query.setString("prodName", "%"+storage.getProduct().getProdName()+"%");
				query.setString("stkWarehouse", "%"+storage.getStkWarehouse()+"%");
				 
				int firstResultIndex=pageSize*(pageNo-1);
				 
				query.setFirstResult(firstResultIndex);
				query.setMaxResults(pageSize);
				 List<Storage> list= query.list();
				return list;
			}});
	}

	public Integer countStorages(final int pageNo, final int pageSize, final Storage storage) {

		 

		String hql="select count(p) from Storage p where p.product.prodName like :prodName " +
		"and p.stkWarehouse like :stkWarehouse";
		List list=getHibernateTemplate().findByNamedParam(hql, new String[]{"prodName","stkWarehouse"}
		, new String[]{"%"+storage.getProduct().getProdName()+"%","%"+storage.getStkWarehouse()+"%"});
	 
		if (list!=null) {
			return (Integer) list.get(0);
		}
		
		return 0;
		
	}

}
